package com.poi.demo.excel;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.poi.demo.FileKit;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

/**
 * excel 文件导入
 * <p>
 * 王永吉
 */
public class ExcelImport {

    public static void main(String[] args) throws Exception {
        //最简单的解析上传数据
        List<LinkedHashMap<String, Object>> linkedHashMaps = SimpleImport();
    }

    /**
     * 最简单的解析
     * 不存在合并单元格或其他情况
     *
     * @return
     */
    private static List<LinkedHashMap<String, Object>> SimpleImport() throws Exception {
        String deskTopPath = FileKit.deskTopPath();
        String templatePath = deskTopPath + "/template.xlsx";
        File file = new File(templatePath);
        FileInputStream fileInputStream = new FileInputStream(file);

        //返回的数据
        List<LinkedHashMap<String, Object>> allSheets = new ArrayList<LinkedHashMap<String, Object>>();
        //获取工作薄
        Workbook wb = ExcelKit.workBook(file.getName(), fileInputStream);
        //获取sheet数量
        int sheetNumber = wb.getNumberOfSheets();
        System.out.println("一共==> " + sheetNumber + "页");

        //开始解析数据
        for (int i = 0; i < sheetNumber; i++) {
            //当前sheet
            Sheet sheet = wb.getSheetAt(i);
            if (sheet == null) {
                continue;
            }

            //当前页信息
            int lastRowNum = sheet.getLastRowNum() + 1; //一共多少行，从零开始
            LinkedHashMap<String, Object> sheetEntity = new LinkedHashMap<String, Object>();
            sheetEntity.put("sheetName", sheet.getSheetName());
            sheetEntity.put("firstRowNum", sheet.getFirstRowNum());
            sheetEntity.put("lastRowNum", lastRowNum);

            //拼装头部和sheet信息
            Row rowHeader = sheet.getRow(0);
            short cellNum = rowHeader.getLastCellNum();//一共多少列
            LinkedHashMap<String, Object> sheetHeaders = new LinkedHashMap<String, Object>();
            for (int k = 0; k < cellNum; k++) {
                sheetHeaders.put(k + "", ExcelKit.cellValue(rowHeader.getCell(k)).toString());
            }
            sheetEntity.put("sheetHeadersKey", sheetHeaders.keySet());
            sheetEntity.put("sheetHeaders", sheetHeaders);

            //获取头部以外的数据
            List<LinkedHashMap> sheetData = new ArrayList<LinkedHashMap>();
            for (int j = 1; j < lastRowNum; j++) {
                //hashMap 换成对应的实体类
                LinkedHashMap<String, Object> hashMap = new LinkedHashMap<String, Object>();
                for (int k = 0; k < cellNum; k++) {
                    hashMap.put(k + "", ExcelKit.cellValue(sheet.getRow(j).getCell(k)));
                }
                sheetData.add(hashMap);
            }

            //
            sheetEntity.put("sheetData", sheetData);
            allSheets.add(sheetEntity);
        }

        System.out.println("解析返回数据==> " + JSON.toJSONString(allSheets));

        LinkedHashMap<String, Object> sheet1 = allSheets.get(0);
        Object sheetData = sheet1.get("sheetData");
        JSONArray arrayData = JSONArray.parseArray(JSON.toJSONString(sheetData));
        System.out.println("解析第一页的数据==> " + arrayData.toJSONString());


        return allSheets;
    }

}
